﻿using System.Data.SqlClient;
using System;
using System.Data;
namespace DataAccess.ArchivesTableAdapters {
    partial class tblDocumentsTableAdapter
    {
        public int AddNewDocument(int docNumber, int dtID, int docSender, string dateOfReceipt, int adoptedBy
                                    , bool inOut, string shortenedDetail, string storePath)
        {
            InitConnection();
            _connection.Open();
            SqlCommand myCommand = new SqlCommand("INSERT INTO tblDocuments (docNumber, dtID, docSender, dateOfReceipt, adoptedBy, inOut, shortenedDetail, storePath, status) values ("+docNumber.ToString()+", "+dtID.ToString()
                                                    +", "+docSender.ToString()+", \'"+dateOfReceipt+"\', "+adoptedBy.ToString()+", "
                                                    +Convert.ToInt32(inOut)+", N\'"+shortenedDetail+"\', \'"+storePath+"\', 0)"
                                     , _connection);
            return myCommand.ExecuteNonQuery();
        }

        public int UpdateDocument(int docNumber, int dtID, int docSender, string dateOfReceipt, int adoptedBy
                                    , bool inOut, string shortenedDetail, string storePath, bool status, int docID)
        {
            string query = "UPDATE tblDocuments SET docNumber=" + docNumber.ToString() + ", dtID=" + dtID.ToString() + ", docSender=" + docSender.ToString()
                          + ", dateOfReceipt=\'" + dateOfReceipt + "\', adoptedBy=" + adoptedBy.ToString() + ", inOut=" + Convert.ToInt32(inOut) + ", shortenedDetail=\'" + shortenedDetail
                          + "\', storePath=\'" + storePath + "\', status=" + Convert.ToInt32(status) + " WHERE docID=" + docID;
            InitConnection();
            _connection.Open();
            SqlCommand myCommand = new SqlCommand(query, _connection);
            return myCommand.ExecuteNonQuery();
        }

        [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
        [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
        [global::System.ComponentModel.DataObjectMethodAttribute(global::System.ComponentModel.DataObjectMethodType.Select, false)]
        public DataTable SearchAllDocuments(string docCode, string fromDate, string toDate, string sender, string receiver, bool? inOut, bool? status)
        {
            InitConnection();
            InitCommandCollection();
            this._commandCollection[8] = new global::System.Data.SqlClient.SqlCommand();
            this._commandCollection[8].Connection = this.Connection;
            string query = "select        tbldocuments.docid, convert(varchar(20), tbldocuments.docnumber) + '/' + tbldepartments.depcode + '-' + tbldoctype.dtcode as doccode, "
                         + " tbldepartments.depname as sender, tbldocuments.inout, tbldocuments.shorteneddetail, tbldocuments.storepath, tbldocuments.status, "
                         + " tbldocuments.dateofreceipt, tblusers.ulname + N' ' + tblusers.ufname as adopter "
                           + " from            tbldocuments inner join "
                            + " tbldepartments on tbldocuments.docsender = tbldepartments.depid inner join "
                         + " tbldoctype on tbldocuments.dtid = tbldoctype.dtid inner join "
                         + " tblusers on tbldocuments.adoptedby = tblusers.uid where 1=1 ";
            //if (receiver != "") 
            //    query =  "SELECT        CONVERT(varchar(20), dc.docNumber) + '/' + dp1.depCode + '-' + dt.dtCode, dp1.depName AS SenderName, dc.dateOfReceipt AS DateOfReceipt,"
            //            + "u.uFName + ' ' + u.uLName AS Adopter, dc.inOut, dc.shortenedDetail, dc.storePath, dc.status"
            //            + " FROM tblRecipients AS rp INNER JOIN "
            //            + "tblDocuments AS dc ON rp.docID = dc.docID INNER JOIN "
            //            + "tblDocType AS dt ON dc.dtID = dt.dtID INNER JOIN "
            //            + "tblDepartments AS dp ON rp.depID = dp.depID INNER JOIN "
            //            + "tblDepartments AS dp1 ON dc.docSender = dp1.depID INNER JOIN "
            //            + "tblUsers AS u ON dc.adoptedBy = u.uID INNER JOIN "
            //            + "tblDepartments AS dp2 ON u.uDepartment = dp2.depID WHERE dp.depID = " + receiver;
            if (docCode != null)
            {
                query += " AND CONVERT(varchar(20), tblDocuments.docNumber) + '/' + tblDepartments.depCode + '-' + tblDocType.dtCode = @docCode";

                this._commandCollection[8].Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@docCode", global::System.Data.SqlDbType.NVarChar,4000, global::System.Data.ParameterDirection.Input, 0, 0, "docCode", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._commandCollection[8].CommandText = query;
                this._commandCollection[8].CommandType = global::System.Data.CommandType.Text;
                this.Adapter.SelectCommand = this.CommandCollection[8];
                this.Adapter.SelectCommand.Parameters["docCode"].Value = docCode;
            }
            if (fromDate != null)
            {
                query += " AND (dc.dateOfReceipt > @fromDate)";
                this._commandCollection[8].Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@fromDate", global::System.Data.SqlDbType.DateTime, 8, global::System.Data.ParameterDirection.Input, 0, 0, "fromDate", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._commandCollection[8].CommandText = query;
                this._commandCollection[8].CommandType = global::System.Data.CommandType.Text;
                this.Adapter.SelectCommand = this.CommandCollection[8];
                this.Adapter.SelectCommand.Parameters["@fromDate"].Value = fromDate;
            }
            if (toDate != null)
            {
                query += " AND (dc.dateOfReceipt < @toDate)";
                this._commandCollection[8].Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@toDate", global::System.Data.SqlDbType.DateTime, 8, global::System.Data.ParameterDirection.Input, 0, 0, "toDate", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._commandCollection[8].CommandText = query;
                this._commandCollection[8].CommandType = global::System.Data.CommandType.Text;
                this.Adapter.SelectCommand = this.CommandCollection[8];
                this.Adapter.SelectCommand.Parameters["@toDate"].Value = toDate;
            }
            if (sender != null)
            {
                query += " AND (tblDepartments.depID = @sender)";
                this._commandCollection[8].Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@sender", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.Input, 0, 0, "sender", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._commandCollection[8].CommandText = query;
                this._commandCollection[8].CommandType = global::System.Data.CommandType.Text;
                this.Adapter.SelectCommand = this.CommandCollection[8];
                this.Adapter.SelectCommand.Parameters["@sender"].Value = sender;
            }
            if (status != null)
            {
                query += " AND (dc.status = @status)";
                this._commandCollection[8].Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@status", global::System.Data.SqlDbType.Bit, 1, global::System.Data.ParameterDirection.Input, 0, 0, "status", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._commandCollection[8].CommandText = query;
                this._commandCollection[8].CommandType = global::System.Data.CommandType.Text;
                this.Adapter.SelectCommand = this.CommandCollection[8];
                this.Adapter.SelectCommand.Parameters["@status"].Value = status;
            }

            this._commandCollection[8].CommandText = query;
            this._commandCollection[8].CommandType = global::System.Data.CommandType.Text;
            this.Adapter.SelectCommand = this.CommandCollection[8];
            Archives.tblDocumentsDataTable dataTable = new Archives.tblDocumentsDataTable();
            this.Adapter.Fill(dataTable);
            return dataTable;
        }
    }

    partial class tblUsersTableAdapter
    {
        public int AddNewUser(int rID, string uFName, string uLName, string uAddress, string uPhone
                                , int uDepartment, int uPosition, string uEmail, string uPassword)
        {
            InitConnection();
            _connection.Open();
            SqlCommand myCommand = new SqlCommand("INSERT INTO tblUsers (rID, uFName, uLName,uAddress, uPhone"
                                                    +", uDepartment, uPosition, uEmail, uPassword) VALUES (" 
                                                    + rID.ToString() + ",N\'" + uFName + "\',N\'"+uLName+"',N\'"
                                                    +uAddress+"\',N\'"+uPhone+"\',"+uDepartment.ToString()+","+uPosition.ToString()
                                                    +",N\'"+uEmail+"\',N\'"+uPassword+"\')"
                                     , _connection);
            return myCommand.ExecuteNonQuery();
        }

        public int UpdateUser(int uID,int rID, string uFName, string uLName, string uAddress, string uPhone
                                , int uDepartment, int uPosition, string uEmail, string uPassword)
        {
            string query = "UPDATE tblUsers SET rID= " +rID.ToString()+ ", uFName=N\'"+uFName+"\', uLName=N\'"+uLName+"\', uAddress=N\'"
                            +uAddress+"\', uPhone=\'"+uPhone+"\', uDepartment="+uDepartment.ToString()+", uPosition="+uPosition.ToString()
                            +", uEmail=\'"+uEmail+"\', uPassword=\'"+uPassword+"' WHERE uID="+uID;
            InitConnection();
            _connection.Open();
            SqlCommand myCommand = new SqlCommand(query, _connection);
            return myCommand.ExecuteNonQuery();
        }
    }

    partial class tblDepartmentsTableAdapter
    {
        public int AddNewDepartment(string depName, string depCode)
        {
            InitConnection();
            _connection.Open();
            SqlCommand myCommand = new SqlCommand("INSERT INTO tblDepartments (depName, depCode) VALUES (N\'" + depName + "\',\'" + depCode + "\')"
                                     , _connection);
            return myCommand.ExecuteNonQuery();
        }

        public int UpdateDepartment(string depName, string depCode, int depID)
        {
            string query = "UPDATE tblDepartments SET depName = N\'" + depName + "\', depCode = \'" + depCode + "\' WHERE depID = " + depID.ToString();
            InitConnection();
            _connection.Open();
            SqlCommand myCommand = new SqlCommand(query, _connection);
            return myCommand.ExecuteNonQuery();
        }
    }
    
    public partial class tblPositionTableAdapter {
        public int AddNewPosition(string pName, string pCode)
        {
            InitConnection();
            _connection.Open();
            SqlCommand myCommand = new SqlCommand("INSERT INTO tblPosition (pName, pCode) VALUES (N\'"+ pName + "\',\'" + pCode + "\')"
                                     , _connection);
            return myCommand.ExecuteNonQuery();
        }

        public int UpdatePosition(string pName, string pCode, int pID) {
            string query = "UPDATE tblPosition SET pName = N\'" + pName + "\', pCode = \'" + pCode + "\' WHERE pID = " + pID.ToString();
            InitConnection();
            _connection.Open();
            SqlCommand myCommand = new SqlCommand(query, _connection);
            return myCommand.ExecuteNonQuery();
        }
    }
}



namespace DataAccess {
    
    
    public partial class Archives {
    }
}
